package at.fhj.itm10.ticketmaster.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.List;

import org.apache.log4j.Logger;

import at.fhj.itm10.ticketmaster.entities.Event;

/**
 * Event DAO.
 * 
 * @author Benjamin Hirt
 */
public class EventDAO extends AbstractDAO {
	
	/**
	 * Must be an integer in the database.
	 */
	private final static String PRIMARYKEY_NAME = "id";
	
	private final static String SQL_INSERT ="INSERT INTO `event` (`name`, `date_time`, `primeevent`, `ticket_price`, `ticket_amount`," +
			"`standing_amount`, `seat_amount`, `loge_amount`, `standing_price`, `seat_price`, `loge_price`, `event_canceled`, " +
			"`description`, `enter`, `begin`, `flyer`, `fk_address_id`, `fk_customer_id`, `fk_eventtype_id`) " +
			"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
	
	private final static String SQL_UPDATE = "UPDATE `event` SET `name` = ?, `date_time` = ?, `primeevent` = ?, `ticket_price` = ?, `ticket_amount` = ?," +
			"`standing_amount` = ?, `seat_amount` = ?, `loge_amount` = ?, `standing_price` = ?, `seat_price` = ?, `loge_price` = ?," +
			" `event_canceled` = ?, `description` = ?, `enter` = ?, `begin` = ?, `flyer` = ?, `fk_address_id` = ?, `fk_customer_id` = ?," +
			" `fk_eventtype_id` = ? WHERE `id` = ?";
	
	private final static String SQL_DELETE = "DELETE FROM `event` WHERE `id` = ?";
	
	private final static String SQL_FINDBYID = "SELECT * FROM `event` WHERE `id` = ?";
	
	private final static String SQL_FINDALL = "SELECT * FROM `event`";

	private final static String SQL_FINDCATEGORY = "SELECT * FROM `event` WHERE `event`.`fk_eventtype_id` = ?";
	
	private final static String SQL_FINDDATE = "SELECT * FROM `event` WHERE `date_time` BETWEEN ? AND ?";
	
	private final static String SQL_FINDADDRESS =  "SELECT * FROM `event` WHERE `event`.`fk_address_id` =?";
	
	private final static String SQL_FINDNAME =  "SELECT * FROM `event` WHERE `event`.`name` like ?";
	
	private final static String SQL_FINDTOPEVENTS =  "SELECT * FROM `event` ORDER BY `event`.`ticket_amount` DESC LIMIT 3";
	
	private final static String SQL_FINDSAMEEVENTS =  "SELECT * FROM `event` event INNER JOIN `eventtype` type ON type.id = event.fk_eventtype_id WHERE NOT event.id = ? ORDER BY `event`.`ticket_amount` LIMIT 6" ;
	
	private final static String SQL_FINDRANDOMEVENTS =  "SELECT * FROM `event` ORDER BY RAND() LIMIT 6";

	private static final String SQL_FINDEVENTBYCUSTOMER = "SELECT * FROM `event` event INNER JOIN `customer` c ON event.fk_customer_id = c.id  WHERE event.fk_customer_id = ?";
	
	private Logger logger = Logger.getLogger(EventDAO.class);
	
	public EventDAO(Connection connection) throws DAOException {
		super(connection);
	}
	
	/**
	 * Inserts Event-entities into DB
	 * 
	 * @param entity
	 * @throws DAOException
	 * 
	 */

	public void insert(Event entity) throws DAOException {
		checkForValidEntity(entity);
		logger.debug("EventDAO.insert(Event = " + entity.toString() + ")");
		PreparedStatement statement = prepareStatement(SQL_INSERT);
		
		try {
			statement.setString(1,entity.getName());
			statement.setString(2,entity.getDateTime());
			statement.setInt(3,entity.getPrimeevent());
			statement.setDouble(4,entity.getTicketPrice());
			statement.setDouble(5,entity.getTicketAmount());
			statement.setInt(6,entity.getStandingAmount());
			statement.setInt(7,entity.getSeatAmount());
			statement.setInt(8, entity.getLogeAmount());
			statement.setDouble(9,entity.getStandingPrice());
			statement.setDouble(10,entity.getSeatPrice());
			statement.setDouble(11, entity.getLogePrice());
			statement.setInt(12,entity.getEventCanceled());
			statement.setString(13,entity.getDescription());
			statement.setString(14,entity.getEnter());
			statement.setString(15,entity.getBegin());
			statement.setString(16,entity.getFlyer());
			statement.setLong(17,entity.getFkAddressId());
			statement.setInt(18,entity.getFkCustomerId());
			statement.setInt(19,entity.getFkEventtypeId());
		} catch (Exception e) {
			logger.error("failed to insert: " + entity.toString());
			throw new DAOException(e);
		}
		
		super.processInsert(statement, entity);
	}

	/**
	 * Updates Event-entities in DB
	 * 
	 * @param entity
	 * @throws DAOException
	 * 
	 */
	public void update(Event entity) throws DAOException {
		checkForValidEntity(entity);
		logger.debug("EventDAO.update(Event = " + entity.toString() + ")");
		if(entity.getId() == 0){
			throw new DAOException("id = 0");
		}
		
		PreparedStatement statement = prepareStatement(SQL_UPDATE);
		
		try {
			statement.setString(1,entity.getName());
			statement.setString(2,entity.getDateTime());
			statement.setInt(3,entity.getPrimeevent());
			statement.setDouble(4,entity.getTicketPrice());
			statement.setDouble(5,entity.getTicketAmount());
			statement.setInt(6,entity.getStandingAmount());
			statement.setInt(7,entity.getSeatAmount());
			statement.setInt(8, entity.getLogeAmount());
			statement.setDouble(9,entity.getStandingPrice());
			statement.setDouble(10,entity.getSeatPrice());
			statement.setDouble(11, entity.getLogePrice());
			statement.setInt(12,entity.getEventCanceled());
			statement.setString(13,entity.getDescription());
			statement.setString(14,entity.getEnter());
			statement.setString(15,entity.getBegin());
			statement.setString(16,entity.getFlyer());
			statement.setLong(17,entity.getFkAddressId());
			statement.setInt(18,entity.getFkCustomerId());
			statement.setInt(19,entity.getFkEventtypeId());
			
			statement.setLong(20, entity.getId());
		} catch (Exception e) {
			logger.error("failed to update: " + entity.toString());
			throw new DAOException(e);
		}
		
		super.processUpdate(statement, entity);
	}

	/**
	 * Deletes Event-entity from DB
	 * 
	 * @param entity
	 * @throws DAOException
	 * 
	 */
	public void delete(Event entity) throws DAOException {
		logger.debug("EventDAO.delete(event = " + entity.toString() + ")");
		
		checkForValidEntity(entity);

		super.doDelete(prepareStatement(SQL_DELETE), entity);
	}

	/**
	 * Searching for Event-entities in DB
	 * 
	 * @param id
	 * @throws DAOException
	 * @return Event
	 * 
	 */
	public Event findById(long id) throws DAOException {
		Event entity = (Event) retrieveEntity(id);
		if (entity != null) {
			return entity;
		}

		return (Event) super.doFindOne(id, prepareStatement(SQL_FINDBYID));
	}

	/**
	 * Searching for all Event-entities in DB
	 * 
	 * @throws DAOException
	 * @return List<Event>
	 * 
	 */
	public List<Event> findAll() throws DAOException {
		return super.doFindMore(PRIMARYKEY_NAME, prepareStatement(SQL_FINDALL));
	}

	/**
	 * {@inheritDoc}
	 */
	protected Event fillEntityByResult(ResultSet resultSet) throws DAOException {
		Event event = new Event();
		
		try {
			event.setId(resultSet.getLong(PRIMARYKEY_NAME));
			event.setName(resultSet.getString("name"));
			event.setDateTime(resultSet.getString("date_time"));
			event.setPrimeevent(resultSet.getInt("primeevent"));
			event.setTicketPrice(resultSet.getDouble("ticket_price"));
			event.setStandingAmount(resultSet.getInt("standing_amount"));
			event.setSeatAmount(resultSet.getInt("seat_amount"));
			event.setLogeAmount(resultSet.getInt("loge_amount"));			
			event.setStandingPrice(resultSet.getInt("standing_price"));
			event.setSeatPrice(resultSet.getInt("seat_price"));
			event.setLogePrice(resultSet.getInt("loge_price"));
			event.setEventCanceled(resultSet.getInt("event_canceled"));
			event.setDescription(resultSet.getString("description"));
			event.setEnter(resultSet.getString("enter"));
			event.setBegin(resultSet.getString("begin"));
			event.setFlyer(resultSet.getString("flyer"));
			event.setFkAddressId(resultSet.getInt("fk_address_id"));
			event.setFkCustomerId(resultSet.getInt("fk_customer_id"));
			event.setFkEventtypeId(resultSet.getInt("fk_eventtype_id"));
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			throw new DAOException(e);
		}

		return event;
	}
	
	/*
	 * normal Search
	 * 
	 */
	
	/**
	 * Normal search
	 * Searching for Event-entity via eventname
	 * 
	 * @param eventname
	 * @throws DAOException
	 * @return List<Event>
	 * 
	 */
	public List<Event> findByName(String eventname) throws DAOException {
		if (eventname == null) {
			throw new NullPointerException("eventname is null");
		}
		
		eventname = "%"+eventname+"%";
		PreparedStatement statement = prepareStatement(SQL_FINDNAME);

		try {
			statement.setString(1, eventname);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		return super.doFindMore(PRIMARYKEY_NAME, statement);
	}
	
	
	/**
	 * Extended Search
	 * Searching for Event-entity via category-id
	 * 
	 * @param c_id
	 * @throws DAOException
	 * @return List<Event>
	 * 
	 */
	
	public List<Event> findByCategory(long c_id) throws DAOException {		
		PreparedStatement statement = prepareStatement(SQL_FINDCATEGORY);

		try {
			if(c_id == 0){
				throw new NullPointerException();
			}
			
			statement.setLong(1, c_id);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		return super.doFindMore(PRIMARYKEY_NAME, statement);
	}

	/**
	 * Extended Search
	 * Searching for Event-entity via start- and enddate
	 * 
	 * @param start, end
	 * @throws DAOException
	 * @return List<Event>
	 * 
	 */
	public List<Event> findByDate(String start, String end) throws DAOException {
		if (start == null || end == null) {
			throw new NullPointerException("start or end is null");
		}
		
		PreparedStatement statement = prepareStatement(SQL_FINDDATE);

		try {
			statement.setString(1, start);
			statement.setString(2, end);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		return super.doFindMore(PRIMARYKEY_NAME, statement);
	}
	
	/**
	 * Extended Search
	 * Searching for Event-entity via address-id
	 * 
	 * @param id
	 * @throws DAOException
	 * @return List<Event>
	 * 
	 */
	public List<Event> findByAddress(long id) throws DAOException{
		PreparedStatement statement = prepareStatement(SQL_FINDADDRESS);

		try {
			if(id == 0){
				throw new NullPointerException();
			}
			
			statement.setLong(1, id);
		} catch (Exception e) {
			throw new DAOException(e);
		}
		
		return super.doFindMore(PRIMARYKEY_NAME, statement);
		
	}
	
	/**
	 * 
	 * Searching for all Top Event-entities
	 * 
	 * @throws DAOException
	 * @return List<Event>
	 * 
	 */
	 public List<Event> findTopEvents() throws DAOException{
		  return super.doFindMore(PRIMARYKEY_NAME, prepareStatement(SQL_FINDTOPEVENTS));
	 }
	 
		/**
		 *
		 * Searching for similar Event-entities via category-id
		 * 
		 * @param id
		 * @throws DAOException
		 * @return List<Event>
		 * 
		 */
	 public List<Event> findSameEvents(long id) throws DAOException{
			PreparedStatement statement = prepareStatement(SQL_FINDSAMEEVENTS);

			try {
				if(id == 0){
					throw new NullPointerException();
				}
				
				statement.setLong(1, id);
			} catch (Exception e) {
				throw new DAOException(e);
			}
			
			return super.doFindMore(PRIMARYKEY_NAME, statement);
	 }
	 
		/**
		 * 
		 * Searching for random Event-entities
		 * 
		 * @throws DAOException
		 * @return List<Event>
		 * 
		 */
	 
	 public List<Event> findRandomEvents() throws DAOException{
		  return super.doFindMore(PRIMARYKEY_NAME, prepareStatement(SQL_FINDRANDOMEVENTS));
	 }
	 
		/**
		 * 
		 * Searching for customer Event-entities via customer-id
		 * 
		 * @param id
		 * @throws DAOException
		 * @return List<Event>
		 * 
		 */
	 public List<Event> findByCustomer(long id) throws DAOException{
		 PreparedStatement statement = prepareStatement(SQL_FINDEVENTBYCUSTOMER); 
		 
			try {
				if(id == 0){
					throw new NullPointerException();
				}
				
				statement.setLong(1, id);
			} catch (Exception e) {
				throw new DAOException(e);
			}
		 
		 return super.doFindMore(PRIMARYKEY_NAME, statement); 
	 }
}
